import sqlite3
import datetime

# 初始化数据库连接
conn = sqlite3.connect('warehouse_management.db')
cursor = conn.cursor()

# 创建物品表
cursor.execute('''
CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tag_id TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT,
    quantity INTEGER NOT NULL DEFAULT 0,
    location TEXT NOT NULL,
    unit_price REAL DEFAULT 0.0,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
)
''')

# 创建出入库记录表
cursor.execute('''
CREATE TABLE IF NOT EXISTS inventory_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tag_id TEXT NOT NULL,
    item_name TEXT NOT NULL,
    action TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    location TEXT NOT NULL,
    reader_id INTEGER,
    timestamp TIMESTAMP NOT NULL,
    operator TEXT,
    remarks TEXT
)
''')

# 创建位置表
cursor.execute('''
CREATE TABLE IF NOT EXISTS locations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    current_count INTEGER DEFAULT 0,
    capacity INTEGER DEFAULT 10,
    description TEXT
)
''')

# 插入默认位置数据
default_locations = [
    ('货架A1', 0, 20, '仓库A区第一层'),
    ('货架A2', 0, 20, '仓库A区第二层'),
    ('货架B1', 0, 20, '仓库B区第一层'),
    ('货架B2', 0, 20, '仓库B区第二层')
]

# 先检查是否已存在位置数据
cursor.execute("SELECT COUNT(*) FROM locations")
location_count = cursor.fetchone()[0]

if location_count == 0:
    cursor.executemany(
        "INSERT INTO locations (name, current_count, capacity, description) VALUES (?, ?, ?, ?)",
        default_locations
    )
    print("已插入默认位置数据")
else:
    print("位置表已有数据，跳过插入")

# 添加一些测试物品数据
test_items = [
    ('11223344', '无线鼠标', '办公用无线鼠标', '办公用品', 15, '货架A1', 49.9, datetime.datetime.now(), datetime.datetime.now()),
    ('22334455', '机械键盘', '游戏机械键盘', '电脑配件', 8, '货架A1', 299.0, datetime.datetime.now(), datetime.datetime.now()),
    ('33445566', '显示器支架', '可调节显示器支架', '电脑配件', 3, '货架A2', 89.9, datetime.datetime.now(), datetime.datetime.now()),
    ('44556677', '打印纸', 'A4打印纸500张', '办公用品', 20, '货架B1', 25.5, datetime.datetime.now(), datetime.datetime.now()),
    ('55667788', 'USB集线器', '4口USB3.0集线器', '电脑配件', 12, '货架B1', 39.9, datetime.datetime.now(), datetime.datetime.now())
]

# 先检查是否已存在物品数据
cursor.execute("SELECT COUNT(*) FROM items")
item_count = cursor.fetchone()[0]

if item_count == 0:
    cursor.executemany(
        "INSERT INTO items (tag_id, name, description, category, quantity, location, unit_price, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        test_items
    )
    print("已插入测试物品数据")
    
    # 更新位置计数
    for item in test_items:
        location = item[5]
        quantity = item[4]
        cursor.execute("UPDATE locations SET current_count = current_count + ? WHERE name = ?", (quantity, location))
    
    # 添加一些测试日志数据
    now = datetime.datetime.now()
    test_logs = [
        ('11223344', '无线鼠标', 'IN', 15, '货架A1', 1, now, '系统', '初始入库'),
        ('22334455', '机械键盘', 'IN', 8, '货架A1', 1, now, '系统', '初始入库'),
        ('33445566', '显示器支架', 'IN', 3, '货架A2', 1, now, '系统', '初始入库'),
        ('44556677', '打印纸', 'IN', 20, '货架B1', 2, now, '系统', '初始入库'),
        ('55667788', 'USB集线器', 'IN', 12, '货架B1', 2, now, '系统', '初始入库')
    ]
    
    cursor.executemany(
        "INSERT INTO inventory_logs (tag_id, item_name, action, quantity, location, reader_id, timestamp, operator, remarks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        test_logs
    )
    print("已插入测试日志数据")
else:
    print("物品表已有数据，跳过插入测试数据")

# 提交并关闭连接
conn.commit()
conn.close()

print("数据库初始化完成！")